package src.org.microblog.dao.impl;

import src.org.microblog.dao.BaseDao;
import src.org.microblog.dao.BlogDao;
import src.org.microblog.entity.Blog;
import src.org.microblog.util.DatabaseUtil;
import src.org.microblog.util.DateUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BlogDaoImpl extends BaseDao implements BlogDao {
    public BlogDaoImpl(Connection conn) {
        super(conn);
    }
    @Override
    public int getBlogCount(int uId) throws SQLException{
        ResultSet rs = null;
        //获取我的微博数
        String sql = " select count(*) from blog where uid = ?" ;
        int count = 0;

        try{
            rs = this.executeQuery(sql, uId);
            while (rs.next()) {
                count = rs.getInt("count(*)");
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            DatabaseUtil.closeAll(null, null, rs);
        }
        return count;
    }

    @Override
    public int getForwardCountByBid(int bid) throws SQLException{
        ResultSet rs = null;
        //获取微博转发数
        String sql = " select count(*) from blog where fromBid = ?" ;
        int count = 0;

        try{
            rs = this.executeQuery(sql, bid);
            while (rs.next()) {
                count = rs.getInt("count(*)");
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            DatabaseUtil.closeAll(null, null, rs);
        }
        return count;
    }

    @Override
    public Blog findBlogById(int id) throws SQLException{
        Blog blog = null;
        ResultSet rs = null;
        String sql = "select b1.id,b1.uid,b1.content,b1.images,b1.time,b1.state, u1.nickname as u_nickname, u1.image as u_image, u1.address as u_address, u1.mysite as u_mysite,b2.id as f_id, b2.content as f_content, b2.images as f_images, u2.nickname as f_nickname from (select * from blog where id=?) b1 left join blog b2  on b1.fromBid = b2.id left join user u1 on b1.uid = u1.id left join user u2 on b2.uid = u2.id ";
        try{
            rs = this.executeQuery(sql,id);
            while (rs.next()) {
                blog = new Blog();
                blog.setId(rs.getInt("id"));
                blog.setuId(rs.getInt("uid"));
                blog.setContent(rs.getString("content"));
                blog.setImages(rs.getString("images"));
                blog.setTime(rs.getTimestamp("time"));
                blog.setState(rs.getInt("state"));
                blog.getUser().setNickname(rs.getString("u_nickname"));
                blog.getUser().setImage(rs.getString("u_image"));
                blog.getUser().setAddress(rs.getString("u_address"));
                blog.getUser().setMysite(rs.getString("u_mysite"));
                if(blog.getState()==2){
                    Blog fBlog = new Blog();
                    //检查转发的原始微博是否被删除
                    if(rs.getObject("f_id")!=null){
                        fBlog.setId(rs.getInt("f_id"));
                        fBlog.getUser().setNickname(rs.getString("f_nickname"));
                        fBlog.setContent(rs.getString("f_content"));
                        fBlog.setImages(rs.getString("f_images"));
                        blog.setOrigin(fBlog);
                    }
                }
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            DatabaseUtil.closeAll(null, null, rs);
        }
        return blog;
    }

    @Override
    public int deleteBlog(int id) throws SQLException{
                String sql = "delete from blog where id = ?";
        int result=0;
        try{
            result = this.executeUpdate(sql, id);
        }catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
        return result;
    }

    @Override
    public int getAllBlogsCount(int uId, String keyword) throws SQLException {
        ResultSet rs = null;
        //获取我和我的关注人的微博总数
        String sql = "select count(*) from blog b1 left join blog b2 on b1.fromBid=b2.id " +
                "where (b1.uid = ? or b1.uid in (select followedUid from follow where uid = ?)) " +
                "and (b1.content like ? or b2.content like ? )";
        int count = 0;
        try{
            rs = this.executeQuery(sql, uId, uId, "%"+keyword+"%", "%"+keyword+"%");
            while (rs.next()) {
                count = rs.getInt("count(*)");
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            DatabaseUtil.closeAll(null, null, rs);
        }
        return count;
    }

    @Override
    public List<Blog> getAllBlogsByPage(int uId, String keyword, int pageNo, int pageSize) throws SQLException {
        List<Blog> list = new ArrayList<Blog>();
        ResultSet rs = null;
        //获取我和我的关注人的所有微博,并按照降序排列
        String fuzzyQuery = ""; //定义模糊查询部分字符串
        if(keyword!=null && !keyword.equals("")){
            fuzzyQuery = "and (b1.content like ? or b2.content like ?)";
        }
        String sql = " select b1.id,b1.uid, b1.content,b1.images,b1.time,b1.state, " +
                "u1.nickname as u_nickname, u1.image as u_image,b2.id as f_id, " +
                "b2.content as f_content, b2.images as f_images, u2.nickname as f_nickname " +
                "from blog b1 left join blog b2 on b1.fromBid = b2.id " +
                "left join user u1 on b1.uid=u1.id " +
                "left join user u2 on b2.uid=u2.id where (b1.uid = ? or b1.uid in " +
                "(select followedUid from follow where uid = ?)) " +
                fuzzyQuery +
                "order by b1.time desc LIMIT ?, ?";

        try{
            if(keyword.equals("")){
                rs = this.executeQuery(sql,uId, uId,(pageNo - 1) * pageSize,pageSize);
            }else{
                rs = this.executeQuery(sql,uId, uId, "%"+keyword+"%", "%"+keyword+"%",
                        (pageNo - 1) * pageSize,pageSize);
            }
            Blog blog = null;
            while (rs.next()) {
                blog = new Blog();
                blog.setId(rs.getInt("id"));
                blog.setuId(rs.getInt("uid"));
                blog.getUser().setNickname(rs.getString("u_nickname"));
                blog.getUser().setImage(rs.getString("u_image"));
                blog.setContent(rs.getString("content"));
                blog.setImages(rs.getString("images"));
                blog.setTime(rs.getTimestamp("time"));
                blog.setState(rs.getInt("state"));
                if(blog.getState()==2){
                    Blog fBlog = new Blog();
                    //检查转发的原始微博是否被删除
                    if(rs.getObject("f_id")!=null){
                        fBlog.setId(rs.getInt("f_id"));
                        fBlog.getUser().setNickname(rs.getString("f_nickname"));
                        fBlog.setContent(rs.getString("f_content"));
                        fBlog.setImages(rs.getString("f_images"));
                        blog.setOrigin(fBlog);
                    }
                }
                list.add(blog);
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            DatabaseUtil.closeAll(null, null, rs);
        }
        return list;
    }

    @Override
    public int addNewBlog(Blog blog) throws SQLException {
        String now = DateUtil.now();
        String sql = "insert into blog (uid, content, images, time, state) VALUES (?, ?, ?, ?, 1);";
        int result=0;
        try{
            result = this.executeUpdate(sql, blog.getuId(),blog.getContent(),blog.getImages(),now);
        }catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
        return result;
    }

    @Override
        public int getPopBlogsCount(int uId, String keyword) throws SQLException {
        ResultSet rs = null;
        //获取微博总数
        String sql = "select count(*) from blog b1 left join blog b2 on b1.fromBid=b2.id left join user u on b1.uid = u.id " +
                "where (u.ispublic = 'T' or (u.ispublic = 'F' and u.id in (select followedUid from follow where uid = ?))) and (b1.content like ? or b2.content like ? ) ";
        int count = 0;
        try{
            rs = this.executeQuery(sql, uId, "%"+keyword+"%", "%"+keyword+"%");
            while (rs.next()) {
                count = rs.getInt("count(*)");
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            DatabaseUtil.closeAll(null, null, rs);
        }
        return count;
    }

    @Override
    public List<Blog> getPopBlogsByPage(int uId, String keyword, int pageNo, int pageSize) throws SQLException {
        List<Blog> list = new ArrayList<Blog>();
        ResultSet rs = null;
        //分页获取一周热门微博列表
        String fuzzyQuery = ""; //定义模糊查询部分字符串
        if(keyword!=null && !keyword.equals("")){
            fuzzyQuery = "and (b1.content like '%" + keyword + "%' or b3.content like '%" + keyword + "%' ) " ;
        }

        //统计一周内的热度排名
        String sql = "select b1.id, b1.uid, b1.content, b1.images, b1.time, b1.state, u1.nickname as u_nickname, u1.image as u_image, b3.id as f_id, b3.content as f_content, u2.nickname as f_nickname, b3.images as f_images, b1.fromBid, (count(distinct(c.id)) + count(distinct(l.id)) + count(distinct(b2.id))) as pop_count " +
                "from blog b1 left join comment as c on b1.id = c.bid and DATEDIFF(now(),c.time)<=7 " +
                "left join likes  as l on b1.id = l.bid and DATEDIFF(now(), l.time)<=7 " +
                "left join blog as b2 on b2.fromBid = b1.id and DATEDIFF(now(),b2.time)<=7 " +
                "left join user as u1 on b1.uid = u1.id " +
                "left join blog as b3 on b3.id = b1.fromBid " +
                "left join user as u2 on b3.uid = u2.id " +
                "where (u1.ispublic = 'T' or (u1.ispublic = 'F' and b1.uid in (select followedUid from follow where uid = ? ))) " +
                fuzzyQuery +
                "group by b1.id  " +
                "order by pop_count desc LIMIT ?, ?";
        try{
            rs = this.executeQuery(sql,uId, (pageNo - 1) * pageSize,pageSize);
            Blog blog = null;
            while (rs.next()) {
                blog = new Blog();
                blog.setId(rs.getInt("id"));
                blog.setuId(rs.getInt("uid"));
                blog.getUser().setNickname(rs.getString("u_nickname"));
                blog.getUser().setImage(rs.getString("u_image"));
                blog.setContent(rs.getString("content"));
                blog.setImages(rs.getString("images"));
                blog.setTime(rs.getTimestamp("time"));
                blog.setState(rs.getInt("state"));

                if(blog.getState()==2){
                    Blog fBlog = new Blog();
                    //检查转发的原始微博是否被删除
                    if(rs.getObject("f_id")!=null){
                        fBlog.setId(rs.getInt("f_id"));
                        fBlog.getUser().setNickname(rs.getString("f_nickname"));
                        fBlog.setContent(rs.getString("f_content"));
                        fBlog.setImages(rs.getString("f_images"));
                        blog.setOrigin(fBlog);
                    }
                }
                list.add(blog);
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            DatabaseUtil.closeAll(null, null, rs);
        }
        return list;
    }

    @Override
    public int getBlogCountByUidAndKeyword(int uId, String keyword) throws SQLException {
        ResultSet rs = null;
        //获取我的微博数
        String sql = "select count(*) from blog b1 left join blog b2 on b1.fromBid=b2.id " +
                "where b1.uid = ? and b1.content like ? or b2.content like ? ";
        int count = 0;

        try{
            rs = this.executeQuery(sql, uId, "%"+keyword+"%", "%"+keyword+"%");
            while (rs.next()) {
                count = rs.getInt("count(*)");
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            DatabaseUtil.closeAll(null, null, rs);
        }
        return count;
    }

    @Override
    public List<Blog> getMyBlogsByPage(int uId, String keyword, int pageNo, int pageSize) throws SQLException {
        List<Blog> list = new ArrayList<Blog>();
        ResultSet rs = null;
        //分页获取我的所有微博,并按照降序排列
        String sql = "select b1.id,b1.uid,b1.content,b1.images,b1.time,b1.state,b2.id as f_id, b2.content as f_content, " +
                "b2.images as f_images, user.nickname as f_nickname from blog b1 left join blog b2  on b1.fromBid = b2.id " +
                "left join user on b2.uid = user.id where b1.uid = ? and (b1.content like ? or b2.content like ?) order by b1.time desc LIMIT ?, ? ";

        try{
            rs = this.executeQuery(sql,uId,"%"+keyword+"%","%"+keyword+"%",(pageNo - 1) * pageSize,pageSize);
            Blog blog = null;
            while (rs.next()) {
                blog = new Blog();
                blog.setId(rs.getInt("id"));
                blog.setuId(rs.getInt("uid"));
                blog.setContent(rs.getString("content"));
                blog.setImages(rs.getString("images"));
                blog.setTime(rs.getTimestamp("time"));
                blog.setState(rs.getInt("state"));
                if(blog.getState()==2){
                    Blog fBlog = new Blog();
                    //检查转发的原始微博是否被删除
                    if(rs.getObject("f_id")!=null){
                        fBlog.setId(rs.getInt("f_id"));
                        fBlog.getUser().setNickname(rs.getString("f_nickname"));
                        fBlog.setContent(rs.getString("f_content"));
                        fBlog.setImages(rs.getString("f_images"));
                        blog.setOrigin(fBlog);
                    }
                }
                list.add(blog);
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            DatabaseUtil.closeAll(null, null, rs);
        }
        return list;
    }

    @Override
    public int forwardBlog(int uId, String content, int fromBid) throws SQLException {
        String now = DateUtil.now();
        String sql = "insert into blog (uid, content, time, state, fromBid) VALUES (?, ?, ?, ?, ?);";
        int result=0;
        try{
            result = this.executeUpdate(sql, uId,content,now,2, fromBid);
        }catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
        return result;
    }
}
